/**
 * 	 DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *   @author Colin Redmond
 *   Copyright (C) 2011 Colin Redmond
 *
 *   Licensed under the Apache License, Version 2.0 (the "License");
 *   you may not use this file except in compliance with the License.
 *   You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *   Unless required by applicable law or agreed to in writing, software
 *   distributed under the License is distributed on an "AS IS" BASIS,
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *   See the License for the specific language governing permissions and
 *   limitations under the License.
 */

package com.cred.industries.platform.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cred.industries.core.database.DBConnectionManager;
import com.cred.industries.core.exceptions.ApplicationException;
import com.cred.industries.core.transferObj.result.ReturnCodes;
import com.cred.industries.platform.business.objects.CustomerBO;
;

/**
 * manages all DB interaction for the Customer. It takes in 
 * customer BO's to update/create the DB
 * Note this class has no modify so it is only accessible in this package
 */
class CustomerMySQLDAO implements ICustomerDAO {

	//represent the names of the database columns
	private static final String CUSTOMERID_COLUMN = "customerId";
	private static final String PERSONA_NAME_COLUMN = "personaName";
	private static final String PASSWORD_COLUMN = "password";
	private static final String FIRST_NAME_COLUMN = "firstName";
	private static final String LAST_NAME_COLUMN = "LastName";
	private static final String EMAILADDRESS_COLUMN = "emailAddress";
	

	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.ICustomerDAO#insert(com.cred.industries.platform.business.objects.CustomerBO)
	 */
	@Override
	public void insert(CustomerBO cust) {
		
		// Setup the connection with the DB
		try (Connection connect = DBConnectionManager.getInstance().getConnection()) {
			
			insert(connect, cust);
		} catch (SQLException e) {
			throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "unable to get DB connection when CustomerDAO.insert" + cust.getPersonaName());
		}

	}
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.ICustomerDAO#insert(java.sql.Connection, com.cred.industries.platform.business.objects.CustomerBO)
	 */
	@Override
	public void insert(Connection connect, CustomerBO cust) {
		
		String sqlQuery = "INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";
		
		// Setup the connection with the DB
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS)) {	 

			preparedStatement.setInt(1, 0);
			preparedStatement.setString(2, cust.getPersonaName());
			preparedStatement.setString(3, cust.getPassword());
			preparedStatement.setString(4, cust.getFirstName());
			preparedStatement.setString(5, cust.getLastName());
			preparedStatement.setString(6, cust.getEmailAddress());
			
			//verify that we created a customer
			int created = preparedStatement.executeUpdate();
			if(created == 0) {
				throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no customers created " + cust.getPersonaName());
			}
			try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
				
				if(resultSet.next()) {
					cust.setCustomerId(resultSet.getInt(1));
				} else {
					throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no customerID generated " + cust.getPersonaName());
				}
			} 
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to create " + cust.getPersonaName());
		}
	}
	
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.ICustomerDAO#update(com.cred.industries.platform.business.objects.CustomerBO)
	 */
	@Override
	public void update(CustomerBO cust) {
		
		// Setup the connection with the DB
		try (Connection connect = DBConnectionManager.getInstance().getConnection()) {
			
			update(connect, cust);
		} catch (SQLException e) {
			throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "unable to get DB connection when CustomerDAO.update" + cust.getPersonaName());
		} 
	}
	
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.ICustomerDAO#update(java.sql.Connection, com.cred.industries.platform.business.objects.CustomerBO)
	 */
	@Override
	public void update(Connection connect, CustomerBO cust) {
		
		String sqlQuery = "UPDATE customer SET "
				+ PERSONA_NAME_COLUMN + "=?, "
				+ PASSWORD_COLUMN + "=?, "
				+ FIRST_NAME_COLUMN + "=?, "
				+ LAST_NAME_COLUMN + "=?, "
				+ EMAILADDRESS_COLUMN + "=? "
				+ " WHERE " + CUSTOMERID_COLUMN + "=?";
		
		try (PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)) {
			
			preparedStatement.setString(1, cust.getPersonaName());
			preparedStatement.setString(2, cust.getPassword());
			preparedStatement.setString(3, cust.getFirstName());
			preparedStatement.setString(4, cust.getLastName());
			preparedStatement.setString(5, cust.getEmailAddress());
			preparedStatement.setInt(6, cust.getCustomerId());
			
			//verify that we created a customer
			int updated = preparedStatement.executeUpdate();
			if(updated == 0) {
				throw new ApplicationException(ReturnCodes.DATABASE_ERROR, "no customers updated " + cust.getPersonaName());
			}
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "unable to create " + cust.getPersonaName());
		} 
	}
			
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.ICustomerDAO#findCustomerById(int)
	 */
	@Override
	public CustomerBO findCustomerById(int custIdToFind) {

		String sqlQuery = "SELECT * FROM customer WHERE customerId = ?";
		
		// Setup the connection with the DB
		try (Connection connect = DBConnectionManager.getInstance().getConnection();
				PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)) {

			preparedStatement.setInt(1, custIdToFind);
			
			try (ResultSet resultSet = preparedStatement.executeQuery()) {
				
				//build customer BO from result set
				return createCustomerBO(resultSet);
			}
			
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "Failed findCustomerById with " + custIdToFind);
		}
	}
	
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.ICustomerDAO#findCustomerByPersonaName(java.lang.String)
	 */
	@Override
	public CustomerBO findCustomerByPersonaName(String personaNameToFind) {
		
		String sqlQuery = "SELECT * FROM customer WHERE PersonaName = ?";
		
		// Setup the connection with the DB
		try (Connection connect = DBConnectionManager.getInstance().getConnection();
				PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)) {
			
			preparedStatement.setString(1, personaNameToFind);
			
			try (ResultSet resultSet = preparedStatement.executeQuery()) {
				
				//build customer BO from result set
				return createCustomerBO(resultSet);
			}
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "Failed findCustomerByPersonaName with " + personaNameToFind);
		}
	}
	
	@Override
	public CustomerBO findCustomerByEmail(String emailAddress) {

		String sqlQuery = "SELECT * FROM customer WHERE emailAddress = ?";
		
		// Setup the connection with the DB
		try (Connection connect = DBConnectionManager.getInstance().getConnection();
				PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)) {

			preparedStatement.setString(1, emailAddress);
			
			try (ResultSet resultSet = preparedStatement.executeQuery()) {
				
				//build customer BO from result set
				return createCustomerBO(resultSet);
			}
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "Failed findCustomerByEmail with " + emailAddress);
		}
	}
	
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.ICustomerDAO#findCustomersByPersonaName(java.lang.String, int, int)
	 */
	@Override
	public List<CustomerBO> findCustomersByPersonaName(String personaNameToFind, int setStart, int setSize) {
			
		String sqlQuery = "SELECT * FROM customer WHERE PersonaName like '%" + personaNameToFind + "%'  LIMIT " + setStart + ", " + setSize;
		
		// Setup the connection with the DB
		try (Connection connect = DBConnectionManager.getInstance().getConnection();
				Statement statement = connect.createStatement()) {
			
			try (ResultSet resultSet = statement.executeQuery(sqlQuery)) {
			
				//build customer BO from result set
				return createCustomersBO(resultSet);
			}
		} catch (SQLException e) {
			throw new ApplicationException(e, ReturnCodes.DATABASE_ERROR, "Failed populating from SQL with " + personaNameToFind);
		} 
	}
	
	/* (non-Javadoc)
	 * @see com.cred.industries.platform.dao.ICustomerDAO#doesCustomerExist(java.lang.String)
	 */
	@Override
	public boolean doesCustomerExist(String personaName) throws SQLException {
		
		if(personaName == null) {
			return false;
		}
		
		String sqlQuery = "SELECT * FROM customer WHERE PersonaName = ?";
		try (Connection connect = DBConnectionManager.getInstance().getConnection();
				PreparedStatement preparedStatement = connect.prepareStatement(sqlQuery)) {
			// Setup the connection with the DB
		
			preparedStatement.setString(1, personaName);
			
			try (ResultSet resultSet =  preparedStatement.executeQuery()) {
				
				if(!resultSet.next()) {
					return false;
				}
			}
		}
		
		return true; 
	}
	
	/**
	 * take the resultSet and populate the CustomerBO
	 * @param resultSet the DB result set with the customer BO info
	 * @return customer BO built from result set
	 * @throws SQLException
	 */
	private CustomerBO createCustomerBO(final ResultSet resultSet) throws SQLException {
		
		if(!resultSet.next()) {
			return null;
		}
		
		CustomerBO custBO = new CustomerBO(resultSet.getInt(CUSTOMERID_COLUMN)
									, resultSet.getString(PERSONA_NAME_COLUMN)
									, resultSet.getString(PASSWORD_COLUMN)
									, resultSet.getString(FIRST_NAME_COLUMN)
									, resultSet.getString(LAST_NAME_COLUMN)
									, resultSet.getString(EMAILADDRESS_COLUMN));		
		
		return custBO;
	}
	
	/**
	 * take the resultSet and populate the list of CustomerBO
	 * @param resultSet  the DB result set with the customer BO info
	 * @return the list of CustomerBO built from the result set
	 * @throws SQLException
	 */
	private List<CustomerBO> createCustomersBO(final ResultSet resultSet) throws SQLException {

		List<CustomerBO> custs = new ArrayList<CustomerBO>();
		CustomerBO cust = createCustomerBO(resultSet);
		while(cust != null) {
			custs.add(cust);
			cust = createCustomerBO(resultSet);
		}

		return custs;
	}
}
